﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DataLib.DBUtility;//Please add references
using System.Collections.Generic;

namespace DataLib.DAL
{
	/// <summary>
	/// 数据访问类:ServerArea
	/// </summary>
	public partial class ServerArea
	{
		public ServerArea()
		{}
		#region  BasicMethod

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQL.GetMaxID("id", "ServerArea"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int? id)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from ServerArea");
			strSql.Append(" where ID=@ID");
			SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
			parameters[0].Value = id;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(DataLib.Model.ServerArea model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into ServerArea(");
            strSql.Append("ProjectID,ProjectName,ProjectNum,CustomerID,CustomerName,LookSalesmanUserName,LookSalesmanTrueName,CjopersonUserName,CjopersonTrueName,PowerpersonUserName,PowerpersonTrueName,AreaName,ServerPointName,CreateTime,CreateMan)");
            strSql.Append(" values (");
            strSql.Append("@ProjectID,@ProjectName,@ProjectNum,@CustomerID,@CustomerName,@LookSalesmanUserName,@LookSalesmanTrueName,@CjopersonUserName,@CjopersonTrueName,@PowerpersonUserName,@PowerpersonTrueName,@AreaName,@ServerPointName,@CreateTime,@CreateMan)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@ProjectID", SqlDbType.Int,4),
					new SqlParameter("@ProjectName", SqlDbType.NVarChar,50),
					new SqlParameter("@ProjectNum", SqlDbType.NVarChar,50),
					new SqlParameter("@CustomerID", SqlDbType.Int,4),
					new SqlParameter("@CustomerName", SqlDbType.NVarChar,50),
					new SqlParameter("@LookSalesmanUserName", SqlDbType.NVarChar,500),
					new SqlParameter("@LookSalesmanTrueName", SqlDbType.NVarChar,500),
					new SqlParameter("@CjopersonUserName", SqlDbType.NVarChar,50),
					new SqlParameter("@CjopersonTrueName", SqlDbType.NVarChar,50),
					new SqlParameter("@PowerpersonUserName", SqlDbType.NVarChar,50),
					new SqlParameter("@PowerpersonTrueName", SqlDbType.NVarChar,50),
					new SqlParameter("@AreaName", SqlDbType.NVarChar,50),
					new SqlParameter("@ServerPointName", SqlDbType.NVarChar,50),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@CreateMan", SqlDbType.VarChar,20)};
            parameters[0].Value = model.ProjectID;
            parameters[1].Value = model.ProjectName;
            parameters[2].Value = model.ProjectNum;
            parameters[3].Value = model.CustomerID;
            parameters[4].Value = model.CustomerName;
            parameters[5].Value = model.LookSalesmanUserName;
            parameters[6].Value = model.LookSalesmanTrueName;
            parameters[7].Value = model.CjopersonUserName;
            parameters[8].Value = model.CjopersonTrueName;
            parameters[9].Value = model.PowerpersonUserName;
            parameters[10].Value = model.PowerpersonTrueName;
            parameters[11].Value = model.AreaName;
            parameters[12].Value = model.ServerPointName;
            parameters[13].Value = model.CreateTime;
            parameters[14].Value = model.CreateMan;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(DataLib.Model.ServerArea model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update ServerArea set ");
            strSql.Append("ProjectID=@ProjectID,");
            strSql.Append("ProjectName=@ProjectName,");
            strSql.Append("ProjectNum=@ProjectNum,");
            strSql.Append("CustomerID=@CustomerID,");
            strSql.Append("CustomerName=@CustomerName,");
            strSql.Append("LookSalesmanUserName=@LookSalesmanUserName,");
            strSql.Append("LookSalesmanTrueName=@LookSalesmanTrueName,");
            strSql.Append("CjopersonUserName=@CjopersonUserName,");
            strSql.Append("CjopersonTrueName=@CjopersonTrueName,");
            strSql.Append("PowerpersonUserName=@PowerpersonUserName,");
            strSql.Append("PowerpersonTrueName=@PowerpersonTrueName,");
            strSql.Append("AreaName=@AreaName,");
            strSql.Append("ServerPointName=@ServerPointName,");
            strSql.Append("CreateTime=@CreateTime,");
            strSql.Append("CreateMan=@CreateMan");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ProjectID", SqlDbType.Int,4),
					new SqlParameter("@ProjectName", SqlDbType.NVarChar,50),
					new SqlParameter("@ProjectNum", SqlDbType.NVarChar,50),
					new SqlParameter("@CustomerID", SqlDbType.Int,4),
					new SqlParameter("@CustomerName", SqlDbType.NVarChar,50),
					new SqlParameter("@LookSalesmanUserName", SqlDbType.NVarChar,500),
					new SqlParameter("@LookSalesmanTrueName", SqlDbType.NVarChar,500),
					new SqlParameter("@CjopersonUserName", SqlDbType.NVarChar,50),
					new SqlParameter("@CjopersonTrueName", SqlDbType.NVarChar,50),
					new SqlParameter("@PowerpersonUserName", SqlDbType.NVarChar,50),
					new SqlParameter("@PowerpersonTrueName", SqlDbType.NVarChar,50),
					new SqlParameter("@AreaName", SqlDbType.NVarChar,50),
					new SqlParameter("@ServerPointName", SqlDbType.NVarChar,50),
					new SqlParameter("@CreateTime", SqlDbType.DateTime),
					new SqlParameter("@CreateMan", SqlDbType.VarChar,20),
					new SqlParameter("@ID", SqlDbType.Int,4)};
            parameters[0].Value = model.ProjectID;
            parameters[1].Value = model.ProjectName;
            parameters[2].Value = model.ProjectNum;
            parameters[3].Value = model.CustomerID;
            parameters[4].Value = model.CustomerName;
            parameters[5].Value = model.LookSalesmanUserName;
            parameters[6].Value = model.LookSalesmanTrueName;
            parameters[7].Value = model.CjopersonUserName;
            parameters[8].Value = model.CjopersonTrueName;
            parameters[9].Value = model.PowerpersonUserName;
            parameters[10].Value = model.PowerpersonTrueName;
            parameters[11].Value = model.AreaName;
            parameters[12].Value = model.ServerPointName;
            parameters[13].Value = model.CreateTime;
            parameters[14].Value = model.CreateMan;
            parameters[15].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int? id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ServerArea ");
            strSql.Append(" where ID=@ID and p.CustomerID=cum.id and p.ProjectID=sa.ProjectID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from ServerArea ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataLib.Model.ServerArea GetModel(int? id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select top 1 sa.id,sa.ProjectID,sa.ProjectName,sa.ProjectNum,p.CustomerID,cum.customercode,cum.companyname CustomerName,sa.LookSalesmanUserName,sa.LookSalesmanTrueName,sa.CjopersonUserName,sa.CjopersonTrueName,sa.PowerpersonUserName,sa.PowerpersonTrueName,sa.AreaName,sa.ServerPointName,sa.CreateTime,sa.CreateMan ");
            strSql.Append("  from  ServerArea sa,Cumtomer cum,Project p ");
            strSql.Append(" where sa.ID=@ID and p.CustomerID=cum.id and p.ProjectID=sa.ProjectID ");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            DataLib.Model.ServerArea model = new DataLib.Model.ServerArea();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataLib.Model.ServerArea DataRowToModel(DataRow row)
        {
            DataLib.Model.ServerArea model = new DataLib.Model.ServerArea();
            if (row != null)
            {
                if (row["ID"] != null && row["ID"].ToString() != "")
                {
                    model.id = int.Parse(row["ID"].ToString());
                }
                if (row["ProjectID"] != null && row["ProjectID"].ToString() != "")
                {
                    model.ProjectID = int.Parse(row["ProjectID"].ToString());
                }
                if (row["ProjectName"] != null)
                {
                    model.ProjectName = row["ProjectName"].ToString();
                }
                if (row["ProjectNum"] != null)
                {
                    model.ProjectNum = row["ProjectNum"].ToString();
                }
                if (row["CustomerID"] != null && row["CustomerID"].ToString() != "")
                {
                    model.CustomerID = int.Parse(row["CustomerID"].ToString());
                }
                if (row["CustomerName"] != null)
                {
                    model.CustomerName = row["CustomerName"].ToString();
                }
                if (row["customercode"] != null)
                {
                    model.Customercode = row["customercode"].ToString();
                }
                if (row["LookSalesmanUserName"] != null)
                {
                    model.LookSalesmanUserName = row["LookSalesmanUserName"].ToString();
                }
                if (row["LookSalesmanTrueName"] != null)
                {
                    model.LookSalesmanTrueName = row["LookSalesmanTrueName"].ToString();
                }
                if (row["CjopersonUserName"] != null)
                {
                    model.CjopersonUserName = row["CjopersonUserName"].ToString();
                }
                if (row["CjopersonTrueName"] != null)
                {
                    model.CjopersonTrueName = row["CjopersonTrueName"].ToString();
                }
                if (row["PowerpersonUserName"] != null)
                {
                    model.PowerpersonUserName = row["PowerpersonUserName"].ToString();
                }
                if (row["PowerpersonTrueName"] != null)
                {
                    model.PowerpersonTrueName = row["PowerpersonTrueName"].ToString();
                }
                if (row["AreaName"] != null)
                {
                    model.AreaName = row["AreaName"].ToString();
                }
                if (row["ServerPointName"] != null)
                {
                    model.ServerPointName = row["ServerPointName"].ToString();
                }
                if (row["CreateTime"] != null && row["CreateTime"].ToString() != "")
                {
                    model.CreateTime = DateTime.Parse(row["CreateTime"].ToString());
                }
                if (row["CreateMan"] != null)
                {
                    model.CreateMan = row["CreateMan"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select sa.id,sa.ProjectID,sa.ProjectName,sa.ProjectNum,p.CustomerID,cum.customercode,cum.companyname CustomerName,sa.LookSalesmanUserName,sa.LookSalesmanTrueName,sa.CjopersonUserName,sa.CjopersonTrueName,sa.PowerpersonUserName,sa.PowerpersonTrueName,sa.AreaName,sa.ServerPointName,sa.CreateTime,sa.CreateMan ");
            strSql.Append(" FROM ServerArea sa,Cumtomer cum,Project p ");
            strSql.Append(" WHERE p.CustomerID=cum.id and p.ProjectID=sa.ProjectID and sa.IsDel=0");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ID,ProjectID,ProjectName,ProjectNum,CustomerID,CustomerName,LookSalesmanUserName,LookSalesmanTrueName,CjopersonUserName,CjopersonTrueName,PowerpersonUserName,PowerpersonTrueName,AreaName,ServerPointName,CreateTime,CreateMan ");
            strSql.Append(" FROM ServerArea ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM ServerArea ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.ID desc");
            }
            strSql.Append(")AS Row, T.*  from ServerArea T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "ServerArea";
			parameters[1].Value = "ID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  BasicMethod
		#region  ExtensionMethod

        /// <summary>
        /// 根据区域ID取得对应的业务员的综合字符串
        /// </summary>
        /// <param name="id"></param>
        /// <param name="type">1 查看的,2 开CJO，3显示CJO,4 授权</param>
        /// <returns></returns>
        public string GetStringByID(int? id,int type)
        {
            string strval = "";
            DataTable dt = new DataTable();
            dt = new ServerPoint().GetList("ServerAreaID=" + id.ToString()).Tables[0];
            string citycode = "";
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    citycode = new City().GetModelByName(dt.Rows[i]["ServerPointName"].ToString(), "01").citycode;
                    strval += citycode + "{";
                    switch (type)
                    {
                        case 1:
                            strval += dt.Rows[i]["LookSalesmanUserName"].ToString();
                            strval += ";";
                            strval += dt.Rows[i]["LookSalesmanTrueName"].ToString();
                            strval += "}#";
                            break;
                        case 2:
                            strval += dt.Rows[i]["CjopersonUserName"].ToString();
                            strval += ",";
                            strval += dt.Rows[i]["CjopersonTrueName"].ToString();
                            strval += "};";
                            break;
                        case 3:
                            strval += dt.Rows[i]["Cjoperson_show"].ToString().Replace("@",",").Replace("/",",");
                            strval += "};";
                            break;
                        case 4:
                            strval += dt.Rows[i]["PowerpersonTrueName"].ToString();
                            strval += ",";
                            strval += dt.Rows[i]["PowerpersonUserName"].ToString();
                            strval += "};";
                            break;
                        default:
                            break;
                    }
                }
            }
            return strval;
        }



        /// <summary>
        /// 取得明细point列表 
        /// <param name="samodel">传条件用的model</param>
        /// </summary>
        public DataSet GetPointList(Model.ServerArea samodel)
        {
            StringBuilder strSql = new StringBuilder();
            string strWhere = "";
            if (samodel.AreaName != null)
                strWhere += " and sa.AreaName = '" + samodel.AreaName + "'";
            if (samodel.id != null && samodel.id > 0)
               strWhere += " and sa.id=" + samodel.id.ToString();
            if (samodel.ProjectID != null && samodel.ProjectID != 0)
                strWhere += " and sa.ProjectID=" + samodel.ProjectID;
            if(samodel.ProjectName != null && samodel.ProjectName.Trim()!="")
                strWhere += " and sa.ProjectName='" + samodel.ProjectName + "'";
            if (samodel.ProjectNum != null && samodel.ProjectNum.Trim() != "")
                strWhere += " and sa.ProjectNum='" + samodel.ProjectNum + "'";
            if (samodel.CustomerID != null && samodel.CustomerID != 0)
                strWhere += " and p.CustomerID=" + samodel.CustomerID.ToString();
            if (samodel.CustomerName != null && samodel.CustomerName.Trim() != "")
                strWhere += " and cum.companyname='" + samodel.CustomerName + "'";


            strSql.Append("SELECT sp.ID pointid,sa.ID areaid,p.ProjectID,p.ProjectName,p.ProjectNum,p.CustomerID,cum.customercode,cum.companyname CustomerName,sp.LookSalesmanUserName,sp.LookSalesmanTrueName,sp.CjopersonUserName,sp.CjopersonTrueName,sp.PowerpersonUserName,sp.PowerpersonTrueName,sp.AreaName,sp.ServerPointName,sp.CreateTime,sp.CreateMan,replace(sp.Cjoperson_show,'/','--') Cjoperson_show ");
            strSql.Append(" FROM ServerArea sa,ServerPoint sp,Cumtomer cum,Project p ");
            strSql.Append(" WHERE sp.ServerAreaID=sa.ID and sp.IsDel=0 and p.CustomerID=cum.id and p.ProjectID=sa.ProjectID and sa.IsDel=0");
            strSql.Append(strWhere);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="samodel"></param>
        /// <param name="arrcity">所有服务点</param>
        /// <param name="strview"></param>
        /// <param name="strshow"></param>
        /// <param name="cjocreate"></param>
        /// <param name="strauth"></param>
        /// <returns></returns>
        public int? SaveArea(Model.ServerArea samodel, string[] arrcity, string cjoview, string cjoshow, string cjocreate, string cjoauth)
        {
            int? areaid = 0;
            List<Model.ServerPoint> list = null;
            Model.ServerPoint m = null;
            if (samodel.id > 0)
            {
                areaid = samodel.id;

                list = new ServerPoint().getListModel((int)areaid);
            }
            else 
            {
                areaid = Add(samodel);
                samodel.id = areaid;
            }

            ServerPoint sp = new ServerPoint();
            StringBuilder strSql = new StringBuilder();

            City bcity = new City();
            bool addfalg = true;

            List<Model.ServerPoint> plist = null;
            if (list != null)
            {
                plist = new List<Model.ServerPoint>(list);
            }
            for (int i = 0; i < arrcity.Length; i++)
            {
                if (list != null)
                {
                    foreach (Model.ServerPoint p in list)
                    {
                        //修改服务点
                        if (arrcity[i] != null && arrcity[i].Trim() != "")
                        {
                            if (p.ServerPointName ==arrcity[i])
                            {
                                sp.updatePointByCity(samodel,p.ServerPointName, cjocreate, cjoshow, cjoview, cjoauth);
                                plist.Remove(p);
                                addfalg = false;
                            }
                        }
                    }
                    
                }

                //Add(arr[i]);循环完了没有匹配的就增加
                if (addfalg)
                {
                    m = new Model.ServerPoint();
                    addfalg = true;
                    m.ServerPointName = arrcity[i];
                    sp.AddByCicy(samodel, arrcity[i], cjocreate, cjoshow, cjoview, cjoauth);
                }


            }

            //list 里剩下的删除
            if (list != null)
            {
                string strID = "";
                int r = 0;
                foreach (Model.ServerPoint p in plist)
                {

                    if (r > 0)
                        strID += ",";
                    strID += p.id.ToString();
                    r++;
                }
                if(strID!="")
                    sp.DeleteList(strID);
            }
            return areaid;
        }

		#endregion  ExtensionMethod
	}
}

